from shared import db
from sqlalchemy.dialects.mysql import DECIMAL, TINYINT, TEXT
from datetime import datetime, timezone, timedelta


# Define a timezone for Asia/Shanghai
CST = timezone(timedelta(hours=8), 'Asia/Shanghai')
DTF = '%Y-%m-%d %H:%M:%S'

# 用户表
class User(db.Model):
    __tablename__ = 'user'

    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    account = db.Column(db.String(50), nullable=False, unique=True, comment="账号")
    password = db.Column(db.String(100), nullable=False, comment="密码")
    nickname = db.Column(db.String(50), nullable=False, comment="昵称")
    avatar = db.Column(TEXT, nullable=False, comment="头像")
    sex = db.Column(TINYINT, nullable=False, comment="性别(1男 0女)")
    birthday = db.Column(db.String(50), nullable=False, comment="生日")
    signature = db.Column(TEXT, nullable=False, comment="个性签名")

    created_at = db.Column(db.DateTime, default=lambda: datetime.now(CST))
    updated_at = db.Column(db.DateTime, onupdate=lambda: datetime.now(CST))
    deleted_at = db.Column(db.DateTime, nullable=True)

    roles = db.relationship('Role', backref='user', lazy=True)
    chat_records = db.relationship('ChatRecord', backref='user', lazy=True)

    def to_dict(self):
        return {
            'id': self.id,
            'account': self.account,
            'nickname': self.nickname,
            'avatar': self.avatar,
            'roles': [role.to_dict() for role in self.roles],
            'sex': self.sex,
            'birthday': self.birthday,
            'signature': self.signature,

            'created_at': self.created_at.strftime(DTF),
            'updated_at': self.updated_at.strftime(DTF) if self.updated_at else None,
            'deleted_at': self.deleted_at.strftime(DTF) if self.deleted_at else None
        }

# 角色表
class Role(db.Model):
    __tablename__ = 'role'

    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    user_id = db.Column(db.Integer, db.ForeignKey('user.id'), nullable=False, comment="用户id")

    role_name = db.Column(db.String(50), nullable=False,  comment="角色名称")
    role_avatar = db.Column(TEXT, comment="角色头像")
    role_age = db.Column(db.Integer, comment="角色年龄")
    role_sex = db.Column(TINYINT, comment="角色性别")
    role_description = db.Column(TEXT, comment="角色描述")

    created_at = db.Column(db.DateTime, default=lambda: datetime.now(CST))
    updated_at = db.Column(db.DateTime, onupdate=lambda: datetime.now(CST))
    deleted_at = db.Column(db.DateTime, nullable=True)

    chat_records = db.relationship('ChatRecord', backref='role', lazy=True)

    def to_dict(self):
        return {
            'id': self.id,
            'user_id': self.user_id,
            'role_name': self.role_name,
            'role_avatar': self.role_avatar,
            'role_age': self.role_age,
            'role_sex': self.role_sex,
            'role_description': self.role_description,

            'created_at': self.created_at.strftime(DTF),
            'updated_at': self.updated_at.strftime(DTF) if self.updated_at else None,
            'deleted_at': self.deleted_at.strftime(DTF) if self.deleted_at else None,

            'chat_records': [chat_record.to_dict() for chat_record in self.chat_records]
        }



# 会话记录
class ChatRecord(db.Model):
    __tablename__ = 'chat_record'

    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    user_id = db.Column(db.Integer, db.ForeignKey('user.id'), nullable=False, comment="用户id")
    role_id = db.Column(db.Integer, db.ForeignKey('role.id'), nullable=False, comment="角色id")

    title = db.Column(db.String(255), nullable=False, comment="对话标题")

    created_at = db.Column(db.DateTime, default=lambda: datetime.now(CST))
    deleted_at = db.Column(db.DateTime, nullable=True)

    chat_record_contents = db.relationship('ChatRecordContent', backref='chat_record', lazy=True)

    def to_dict(self):
        return {
            'id': self.id,
            'user_id': self.user_id,
            'role_id': self.role_id,
            'title': self.title,

            'created_at': self.created_at.strftime(DTF),
            'deleted_at': self.deleted_at.strftime(DTF) if self.deleted_at else None,

            'chat_record_contents': [chat_record_content.to_dict() for chat_record_content in self.chat_record_contents]
        }



# 会话记录内容
class ChatRecordContent(db.Model):
    __tablename__ = 'chat_record_content'

    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    chat_record_id = db.Column(db.Integer, db.ForeignKey('chat_record.id'), nullable=False, comment="对话id")
    
    msg_id = db.Column(db.String(255), nullable=False, comment="消息id")
    content = db.Column(TEXT, nullable=False, comment="对话内容")
    sender_type = db.Column(TINYINT, nullable=False, comment="发送者类型(1:AI,2:角色)")

    created_at = db.Column(db.DateTime, default=lambda: datetime.now(CST))

    def to_dict(self):
        return {
            'id': self.id,
            'chat_record_id': self.chat_record_id,
            'msg_id': self.msg_id,
            'content': self.content,
            'sender_type': self.sender_type,
            'created_at': self.created_at.strftime(DTF)
        }